package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.BeneficiaryPayment;

public class DBBeneficiaryPayment implements IFDBBeneficiaryPayment {
	
	private Connection con = DBConnection.getInstance().getDBcon();
	private static DBBeneficiaryPayment dbBen;
	
	private DBBeneficiaryPayment(){}
	
	public static DBBeneficiaryPayment getInstance(){
		if(dbBen==null) dbBen = new DBBeneficiaryPayment();
		return dbBen;
	}
	
	@Override
	public ArrayList<BeneficiaryPayment> getAll() {
		return miscWhere("");
	}
	
	@Override
	public ArrayList<BeneficiaryPayment> getAll_where(String wClause){
		return miscWhere(wClause);
	}
	
	@Override
	public ArrayList<BeneficiaryPayment> searchRandom(String searchF) {
		return miscWhere("paymentId LIKE '"+searchF+"' OR date LIKE '"+searchF+"' OR amount LIKE '"+searchF+"' OR bp.apartId LIKE '"+searchF+"' OR b.buildingName LIKE '"+searchF+"' OR b.buildingNr LIKE '"+searchF+"' OR ap.apartNo LIKE '"+searchF+"' OR ap.noOfRooms LIKE '"+searchF+"' OR ap.landLordId LIKE '"+searchF+"'");
	}

	@Override
	public BeneficiaryPayment getBeneficiaryPayment(int paymentId) {
		return singleWhere("paymentId="+paymentId);
	}
	
	@Override
	public boolean alreadyPaid(Timestamp date){
		boolean paid = false;
		
		String query = "SELECT paymentId FROM BeneficiaryPayment WHERE MONTH(date)=MONTH('"+date+"') AND YEAR(date)=YEAR('"+date+"')";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet rs = stmt.executeQuery(query);
			if(rs.next()) paid=true;
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert the Beneficiary Payment  "+e.getMessage());
		}
		
		return paid;
	}

	@Override
	public int insert(BeneficiaryPayment benPayment) {
		int rc = -1;
			
		String query = "INSERT INTO BeneficiaryPayment (date,amount,type,apartId) VALUES ('"+benPayment.getDate()+"',"+benPayment.getAmount()+",'"+benPayment.getType()+"',"+benPayment.getApartment().getApartId()+")";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert the Beneficiary Payment  "+e.getMessage());
		}
		
		return rc;
	}

	@Override
	public int update(BeneficiaryPayment benPayment) {
		int rc = -1;
		String query = "UPDATE BeneficiaryPayment SET date='"+benPayment.getDate()+"',amount="+benPayment.getAmount()+",type='"+benPayment.getType()+"',apartId="+benPayment.getApartment().getApartId()+" WHERE paymentId="+benPayment.getPaymentId();
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not update the Beneficiary Payment "+e.getMessage());
		}
		
		return rc;
	}

	@Override
	public int delete(int paymentId) {
		int rc = -1;
		String query = "DELETE FROM BeneficiaryPayment WHERE paymentId="+paymentId;
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not delete the Beneficiary Payment "+e.getMessage());
		}
		
		return rc;
	}
	
	
	
	
	
	private BeneficiaryPayment singleWhere(String wClause){
		BeneficiaryPayment benPayment = new BeneficiaryPayment();
		
		ResultSet result;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next()){
				benPayment = buildBeneficiaryPayment(result);
			}
			
			stmt.close();
			
		}
		catch(Exception e){
			System.err.println("Could not execute select Beneficiary Payment query "+e.getMessage());
		}
		
		return benPayment;
	}
	
	
	private ArrayList<BeneficiaryPayment> miscWhere(String wClause){
		ArrayList<BeneficiaryPayment> benPayments = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				benPayments.add(buildBeneficiaryPayment(results));
			}
			
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not execute multiple select Beneficiary Payment query "+e.getMessage());
		}
		
		return benPayments;
	}
	
	
	private String buildQuery(String wClause){
		String query = "SELECT paymentId,date,amount,type,bp.apartId FROM BeneficiaryPayment bp INNER JOIN Apartment ap ON bp.apartId=ap.apartId INNER JOIN Building b ON ap.buildingNr=b.buildingNr";
		
		if(wClause.length()>0) query += " WHERE "+wClause;
		
		return query;
	}
	
	
	private BeneficiaryPayment buildBeneficiaryPayment(ResultSet result){
		BeneficiaryPayment benPayment = new BeneficiaryPayment();
		
		try{
			benPayment.setPaymentId(result.getInt("paymentId"));
			benPayment.setDate(result.getTimestamp("date"));
			benPayment.setAmount(result.getDouble("amount"));
			benPayment.setType(result.getString("type"));
			benPayment.setApartment(DBApartment.getInstance().find(result.getInt("apartId"), false));
		}
		catch(Exception e){
			System.err.println("Could not build the Beneficiary Payment Result "+e.getMessage());
		}
		
		return benPayment;
	}
	
	
	
}
